package com.huabo.contract.mysql.mapper;

import com.hbfk.util.DateUtil;
import com.hbfk.util.PageInfo;
import com.huabo.contract.mysql.entity.TblLegalCloseinformationMySql;

public class TblLegalCloseinformationMySqlMapperSqlConfig {


    public String findListByPageInfo(PageInfo<TblLegalCloseinformationMySql> pageInfo, TblLegalCloseinformationMySql closeInfo,Integer disputeid) throws Exception {
        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,ROWNUM RN FROM (SELECT * FROM TBL_LEGAL_CLOSEINFORMATION tlc\n" +
                "LEFT JOIN TBL_STAFF ts on tlc.CREATESTAFF = ts.STAFFID\n" +
                "LEFT JOIN TBL_LEGAL_DISPUTREGISTRATION tld on tlc.DISPUTINFO = tld.DISPUTEID\n" +
                "WHERE tlc.LINKORG = "+ closeInfo.getLinkorg() );

        if (disputeid != null){
            sqlSb.append(" AND tld.DISPUTEID = " + disputeid );
        }
        if (closeInfo.getRealname() != null && !"".equals(closeInfo.getRealname())) {
            sqlSb.append(" AND ts.REALNAME LIKE '%" + closeInfo.getRealname() + "%'");
        }
        if (closeInfo.getDisputeitem() != null && !"".equals(closeInfo.getDisputeitem())) {
            sqlSb.append(" AND tld.DISPUTEITEM LIKE '%" + closeInfo.getDisputeitem() + "%'");
        }
        if (closeInfo.getMinmoney() != null) {
            sqlSb.append(" AND JUDGEMENTAMOUNT >= " + closeInfo.getMinmoney());
        }

        if (closeInfo.getMaxmoney() != null) {
            sqlSb.append(" AND JUDGEMENTAMOUNT <= " + closeInfo.getMaxmoney());
        }

        sqlSb.append(" ORDER BY closeId DESC) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;
    }


    public String findListByPageInfoCount(TblLegalCloseinformationMySql closeInfo,Integer disputeid) throws Exception {
        StringBuffer sqlSb = new StringBuffer("SELECT COUNT(*) FROM TBL_LEGAL_CLOSEINFORMATION tlc\n" +
                "LEFT JOIN TBL_STAFF ts on tlc.CREATESTAFF = ts.STAFFID\n" +
                "LEFT JOIN TBL_LEGAL_DISPUTREGISTRATION tld on tlc.DISPUTINFO = tld.DISPUTEID\n" +
                "WHERE tlc.LINKORG = "+ closeInfo.getLinkorg() );

        if (disputeid != null){
            sqlSb.append(" AND tld.DISPUTEID = " + disputeid );
        }
        if (closeInfo.getRealname() != null && !"".equals(closeInfo.getRealname())) {
            sqlSb.append(" AND ts.REALNAME LIKE '%" + closeInfo.getRealname() + "%'");
        }
        if (closeInfo.getDisputeitem() != null && !"".equals(closeInfo.getDisputeitem())) {
            sqlSb.append(" AND tld.DISPUTEITEM LIKE '%" + closeInfo.getDisputeitem() + "%'");
        }
        if (closeInfo.getMinmoney() != null) {
            sqlSb.append(" AND JUDGEMENTAMOUNT >= " + closeInfo.getMinmoney());
        }

        if (closeInfo.getMaxmoney() != null) {
            sqlSb.append(" AND JUDGEMENTAMOUNT <= " + closeInfo.getMaxmoney());
        }

        sqlSb.append(" ORDER BY CLOSEID DESC");
        String sql = sqlSb.toString();
        return sql;
    }


    public String addDisputeSettlement(TblLegalCloseinformationMySql closeInfo) {
        StringBuffer column = new StringBuffer("INSERT INTO TBL_LEGAL_CLOSEINFORMATION (CLOSEDATE,CREATETIME");
        StringBuffer value = new StringBuffer(" VALUES (TO_DATE('"+ DateUtil.parseDate(closeInfo.getClosedate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS'),TO_DATE('"+ DateUtil.parseDate(closeInfo.getClosedate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");

//        if(closeInfo.getClosedate() != null && !"".equals(closeInfo.getClosedate())) {
//            column.append(",CLOSEDATE");
//            value.append(",TO_DATE('"+ DateUtil.parseDate(closeInfo.getClosedate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
//        }
        if(closeInfo.getJudgementamount() != null && !"".equals(closeInfo.getJudgementamount())) {
            column.append(",JUDGEMENTAMOUNT");
            value.append(",'"+closeInfo.getJudgementamount()+"'");
        }
        if(closeInfo.getCloseresult() != null && !"".equals(closeInfo.getCloseresult())) {
            column.append(",CLOSERESULT");
            value.append(",'"+closeInfo.getCloseresult()+"'");
        }
        if(closeInfo.getManagerecommond() != null && !"".equals(closeInfo.getManagerecommond())) {
            column.append(",MANAGERECOMMOND");
            value.append(",'"+closeInfo.getManagerecommond()+"'");
        }
        if(closeInfo.getDisputinfo() != null && !"".equals(closeInfo.getDisputinfo())) {
            column.append(",DISPUTINFO");
            value.append(",'"+closeInfo.getDisputinfo()+"'");
        }
        if(closeInfo.getCreatestaff() != null && !"".equals(closeInfo.getCreatestaff())) {
            column.append(",CREATESTAFF");
            value.append(",'"+closeInfo.getCreatestaff()+"'");
        }
//        if(closeInfo.getCreatetime() != null && !"".equals(closeInfo.getCreatetime())) {
//            column.append(",CREATETIME");
//            value.append(",'"+closeInfo.getCreatetime()+"'");
//        }
        if(closeInfo.getLinkorg() != null && !"".equals(closeInfo.getLinkorg())) {
            column.append(",LINKORG");
            value.append(",'"+closeInfo.getLinkorg()+"'");
        }

        column.append(")");
        value.append(")");
        String sql = column.toString()+value.toString();
        return sql;
    }


    public String updateModifyDisputeSettlementModify(TblLegalCloseinformationMySql oldCloseInfo) {
        StringBuffer sql = new StringBuffer("UPDATE TBL_LEGAL_CLOSEINFORMATION SET JUDGEMENTAMOUNT = '"+oldCloseInfo.getJudgementamount()+"'");
        if(oldCloseInfo.getClosedate() != null && !"".equals(oldCloseInfo.getClosedate())) {
            //sql.append(" , CLOSEDATE = '"+oldCloseInfo.getClosedate()+"'");
            sql.append(" ,CLOSEDATE = TO_DATE('"+DateUtil.parseDate(oldCloseInfo.getClosedate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(oldCloseInfo.getCloseresult() != null && !"".equals(oldCloseInfo.getCloseresult())) {
            sql.append(" , CLOSERESULT = '"+oldCloseInfo.getCloseresult()+"'");
        }
        if(oldCloseInfo.getManagerecommond() != null && !"".equals(oldCloseInfo.getManagerecommond())) {
            sql.append(" , MANAGERECOMMOND = '"+oldCloseInfo.getManagerecommond()+"'");
        }
        if(oldCloseInfo.getDisputinfo() != null && !"".equals(oldCloseInfo.getDisputinfo())) {
            sql.append(" , DISPUTINFO = '"+oldCloseInfo.getDisputinfo()+"'");
        }
        if(oldCloseInfo.getCreatestaff() != null && !"".equals(oldCloseInfo.getCreatestaff())) {
            sql.append(" , CREATESTAFF = '"+oldCloseInfo.getCreatestaff()+"'");
        }
        if(oldCloseInfo.getCreatetime() != null && !"".equals(oldCloseInfo.getCreatetime())) {
            sql.append(" ,CREATETIME = TO_DATE('"+DateUtil.parseDate(oldCloseInfo.getCreatetime(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
            //sql.append(" , CREATETIME = '"+oldCloseInfo.getCreatetime()+"'");
        }
        if(oldCloseInfo.getLinkorg() != null && !"".equals(oldCloseInfo.getLinkorg())) {
            sql.append(" , LINKORG = '"+oldCloseInfo.getLinkorg()+"'");
        }


        sql.append(" WHERE CLOSEID = '"+oldCloseInfo.getCloseid()+"'");
        return sql.toString();
    }
}
